﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using YiTu.DBUtility;
namespace TOUR.SZHOME.DAL
{
    /// <summary>
    /// 数据表Activities的数据操作类
    /// </summary>
    public partial class ActivitiesDAL
    {
        #region ConstVariables
        private const string C_TABLE_NAME = "Activities";
        private const string C_SP_ACTIVITIES_FIELDS = "[ID],[Target],[Title],[Img],[Description],[Order],[Link]";
        private const string C_SP_ACTIVITIES_INSERT = "INSERT INTO [Activities]([Target],[Title],[Img],[Description],[Order],[Link]) VALUES(@Target,@Title,@Img,@Description,@Order,@Link);SET @ID = SCOPE_IDENTITY();";
        private const string C_SP_ACTIVITIES_UPDATE = "UPDATE [Activities] SET [Target]=@Target,[Title]=@Title,[Img]=@Img,[Description]=@Description,[Order]=@Order,[Link]=@Link WHERE [ID] = @ID";
        private const string C_SP_ACTIVITIES_DELETE = "DELETE [Activities] WHERE [ID] = @ID";
        private const string C_SP_ACTIVITIES_GET = "SELECT [ID],[Target],[Title],[Img],[Description],[Order],[Link] FROM [Activities] WHERE [ID] = @ID";
        #endregion

        private static string ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["TravelProject"].ConnectionString;
            }
        }

        /// <summary>
        /// 默认构造函数
        /// </summary>
        private ActivitiesDAL() { }

        /// <summary>
        /// 向数据表中插入一条新记录
        /// </summary>
        /// <param name="entity">Entity.ActivitiesEntity实体类</param>
        /// <remarks>如果表存在自增长字段，插入记录成功后自增长字段值会更新至实体</remarks>
        public static bool Insert(Entity.ActivitiesEntity entity)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();
            SqlParameter id_ID = SqlHelper.CreateParam("@ID", SqlDbType.Int, 0, ParameterDirection.Output, null);
            commandParms.Add(id_ID);
            commandParms.Add(SqlHelper.CreateParam("@Target", SqlDbType.NChar, 50, ParameterDirection.Input, entity.Target));
            commandParms.Add(SqlHelper.CreateParam("@Title", SqlDbType.NChar, 100, ParameterDirection.Input, entity.Title));
            commandParms.Add(SqlHelper.CreateParam("@Img", SqlDbType.NChar, 150, ParameterDirection.Input, entity.Img));
            commandParms.Add(SqlHelper.CreateParam("@Description", SqlDbType.NChar, 1000, ParameterDirection.Input, entity.Description));
            commandParms.Add(SqlHelper.CreateParam("@Order", SqlDbType.Int, 0, ParameterDirection.Input, entity.Order));
            commandParms.Add(SqlHelper.CreateParam("@Link", SqlDbType.NChar, 150, ParameterDirection.Input, entity.Link));

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_ACTIVITIES_INSERT, commandParms) > 0 ? true : false;
            //entity.ID = Convert.ToInt32(id_ID.Value);
        }

        /// <summary>
        /// 获取数据库一条记录实体(根据主键条件)
        /// </summary>
        /// <param name="id">活动ID</param>
        /// <returns>Entity.ActivitiesEntity实体类</returns>
        public static Entity.ActivitiesEntity GetByID(int id)
        {
            Entity.ActivitiesEntity entity = null;
            List<SqlParameter> commandParms = new List<SqlParameter>();
            commandParms.Add(SqlHelper.CreateParam("@ID", SqlDbType.Int, 0, ParameterDirection.Input, id));

            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, C_SP_ACTIVITIES_GET, commandParms))
            {
                if (reader.Read())
                {
                    entity = ConvertToEntityFromDataReader(reader);
                }
            }

            return entity;
        }

        /// <summary>
        /// 更新数据库中一条记录(根据主键条件)
        /// </summary>
        /// <param name="entity">Entity.ActivitiesEntity实体类</param>
        public static bool Update(Entity.ActivitiesEntity entity)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();

            commandParms.Add(SqlHelper.CreateParam("@ID", SqlDbType.Int, 0, ParameterDirection.Input, entity.ID));
            commandParms.Add(SqlHelper.CreateParam("@Target", SqlDbType.NChar, 50, ParameterDirection.Input, entity.Target));
            commandParms.Add(SqlHelper.CreateParam("@Title", SqlDbType.NChar, 100, ParameterDirection.Input, entity.Title));
            commandParms.Add(SqlHelper.CreateParam("@Img", SqlDbType.NChar, 150, ParameterDirection.Input, entity.Img));
            commandParms.Add(SqlHelper.CreateParam("@Description", SqlDbType.NChar, 1000, ParameterDirection.Input, entity.Description));
            commandParms.Add(SqlHelper.CreateParam("@Order", SqlDbType.Int, 0, ParameterDirection.Input, entity.Order));
            commandParms.Add(SqlHelper.CreateParam("@Link", SqlDbType.NChar, 150, ParameterDirection.Input, entity.Link));

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_ACTIVITIES_UPDATE, commandParms) > 0 ? true : false;
        }

        /// <summary>
        /// 删除数据库中一条记录(根据主键条件)
        /// </summary>
        /// <param name="id">活动ID</param>
        public static bool Delete(int id)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();
            commandParms.Add(SqlHelper.CreateParam("@ID", SqlDbType.Int, 0, ParameterDirection.Input, id));
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_ACTIVITIES_DELETE, commandParms) > 0 ? true : false;
        }

        /// <summary>
        /// 按条件查询数据表,返回DataTable类型数据
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="dataFields">需返回字段(不能为空,为"*"则返回所有字段)</param>
        /// <param name="orderBy">SQL排序语句(不能为空)，不须带"Order By"关键字</param>
        /// <param name="startRowIndex">记录开始索引，从0开始</param>
        /// <param name="maximumRows">返回记录数量</param>
        /// <returns>DataTable</returns>
        public static DataTable SearchDT(string whereClause, string dataFields, string orderBy, int startRowIndex, int maximumRows)
        {
            if (dataFields.Trim() == "*")
            {
                dataFields = C_SP_ACTIVITIES_FIELDS;
            }
            return SqlListHepler.Search(ConnectionString, C_TABLE_NAME, dataFields, whereClause, orderBy, startRowIndex, maximumRows);
        }


        /// <summary>
        /// 按条件查询数据表,返回 Entity.ActivitiesEntity 数据集
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="orderBy">SQL排序语句(不能为空)，不须带"Order By"关键字</param>
        /// <param name="startRowIndex">记录开始索引，从0开始</param>
        /// <param name="maximumRows">返回记录数量</param>
        public static List<Entity.ActivitiesEntity> Search(string whereClause, string orderBy, int startRowIndex, int maximumRows)
        {
            List<Entity.ActivitiesEntity> list = new List<Entity.ActivitiesEntity>();
            using (SqlDataReader reader = SqlListHepler.SearchDataReader(ConnectionString, C_TABLE_NAME, C_SP_ACTIVITIES_FIELDS, whereClause, orderBy, startRowIndex, maximumRows))
            {
                while (reader.Read())
                {
                    list.Add(ConvertToEntityFromDataReader(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 按条件查询数据表,返回DataTable类型数据
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="dataFields">需返回字段(不能为空,为"*"则返回所有字段)</param>
        /// <param name="orderBy">SQL排序语句(可为空)，不须带"Order By"关键字</param>
        /// <param name="rowsToReturn">返回记录数量</param>
        /// <returns>DataTable</returns>
        public static DataTable SearchDT(string whereClause, string dataFields, string orderBy, int rowsToReturn)
        {
            if (dataFields.Trim() == "*")
            {
                dataFields = C_SP_ACTIVITIES_FIELDS;
            }
            return SqlListHepler.Search(ConnectionString, C_TABLE_NAME, dataFields, whereClause, orderBy, rowsToReturn);
        }

        /// <summary>
        /// 按条件查询数据表,返回 Entity.ActivitiesEntity 数据集
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="orderBy">SQL排序语句(可为空)，不须带"Order By"关键字</param>
        /// <param name="rowsToReturn">返回记录数量</param>
        public static List<Entity.ActivitiesEntity> Search(string whereClause, string orderBy, int rowsToReturn)
        {
            List<Entity.ActivitiesEntity> list = new List<Entity.ActivitiesEntity>();
            using (SqlDataReader reader = SqlListHepler.SearchDataReader(ConnectionString, C_TABLE_NAME, C_SP_ACTIVITIES_FIELDS, whereClause, orderBy, rowsToReturn))
            {
                while (reader.Read())
                {
                    list.Add(ConvertToEntityFromDataReader(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 按条件获取记录数量
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <returns>int整型数据</returns>
        public static int SearchCount(string whereClause)
        {
            return SqlListHepler.SearchCount(ConnectionString, C_TABLE_NAME, whereClause);
        }

        /// <summary>
        /// 转换DataRow类型数据记录为实体
        /// </summary>
        private static Entity.ActivitiesEntity ConvertToEntityFromDataRow(DataRow row)
        {
            Entity.ActivitiesEntity entity = new Entity.ActivitiesEntity();
            entity.ID = Convert.ToInt32(row["ID"]);
            if (Convert.IsDBNull(row["Target"])) entity.Target = null; else entity.Target = row["Target"].ToString();
            if (Convert.IsDBNull(row["Title"])) entity.Title = null; else entity.Title = row["Title"].ToString();
            if (Convert.IsDBNull(row["Img"])) entity.Img = null; else entity.Img = row["Img"].ToString();
            if (Convert.IsDBNull(row["Description"])) entity.Description = null; else entity.Description = row["Description"].ToString();
            if (Convert.IsDBNull(row["Order"])) entity.Order = null; else entity.Order = Convert.ToInt32(row["Order"]);
            if (Convert.IsDBNull(row["Link"])) entity.Link = null; else entity.Link = row["Link"].ToString();

            return entity;
        }

        /// <summary>
        /// 转换SqlDataReader类型数据记录为实体
        /// </summary>
        private static Entity.ActivitiesEntity ConvertToEntityFromDataReader(SqlDataReader reader)
        {
            Entity.ActivitiesEntity entity = new Entity.ActivitiesEntity();
            entity.ID = Convert.ToInt32(reader["ID"]);
            if (Convert.IsDBNull(reader["Target"])) entity.Target = null; else entity.Target = reader["Target"].ToString();
            if (Convert.IsDBNull(reader["Title"])) entity.Title = null; else entity.Title = reader["Title"].ToString();
            if (Convert.IsDBNull(reader["Img"])) entity.Img = null; else entity.Img = reader["Img"].ToString();
            if (Convert.IsDBNull(reader["Description"])) entity.Description = null; else entity.Description = reader["Description"].ToString();
            if (Convert.IsDBNull(reader["Order"])) entity.Order = null; else entity.Order = Convert.ToInt32(reader["Order"]);
            if (Convert.IsDBNull(reader["Link"])) entity.Link = null; else entity.Link = reader["Link"].ToString();

            return entity;
        }

    }
}
